#!/usr/bin/env python3
"""
数据库初始化脚本，整合所有表结构和初始数据，支持Docker环境重复执行
"""
import mysql.connector
import os
import time

def wait_for_mysql():
    """等待MySQL服务启动（最多重试30次，间隔2秒）"""
    max_retries = 30
    retry_count = 0
    
    while retry_count < max_retries:
        try:
            conn = mysql.connector.connect(
                host='mysql',  # Docker Compose中MySQL服务名
                user=os.environ.get('MYSQL_USER', 'root'),
                password=os.environ.get('MYSQL_ROOT_PASSWORD', 'MySQL@123'),
                port=3306
            )
            conn.close()
            print("MySQL服务已启动")
            return True
        except Exception as e:
            print(f"等待MySQL启动中... ({retry_count + 1}/{max_retries})")
            time.sleep(2)
            retry_count += 1
    
    print("MySQL服务启动超时")
    return False

def init_database():
    """初始化数据库表结构和初始数据"""
    if not wait_for_mysql():
        return False
        
    try:
        # 连接MySQL服务器
        conn = mysql.connector.connect(
            host='mysql',
            user=os.environ.get('MYSQL_USER', 'root'),
            password=os.environ.get('MYSQL_ROOT_PASSWORD', 'MySQL@123'),
            port=3306
        )
        cursor = conn.cursor()

        # 创建数据库（若不存在）
        db_name = os.environ.get('MYSQL_DATABASE', 'hotel_management')
        cursor.execute(f"CREATE DATABASE IF NOT EXISTS {db_name} CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci")
        cursor.execute(f"USE {db_name}")

        # 1. 创建基础表（无外键依赖）
        tables = [
            # 用户表
            """
            CREATE TABLE IF NOT EXISTS `users` (
              `id` int(11) NOT NULL AUTO_INCREMENT,
              `username` varchar(50) NOT NULL,
              `password` varchar(255) NOT NULL,
              `email` varchar(100) NOT NULL,
              `phone` varchar(20) DEFAULT NULL,
              `role` enum('user','admin') DEFAULT 'user',
              `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
              PRIMARY KEY (`id`),
              UNIQUE KEY `username` (`username`),
              UNIQUE KEY `email` (`email`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
            """,
            # 房间表
            """
            CREATE TABLE IF NOT EXISTS `rooms` (
              `id` int(11) NOT NULL AUTO_INCREMENT,
              `name` varchar(100) NOT NULL,
              `description` text DEFAULT NULL,
              `price` decimal(10,2) NOT NULL,
              `capacity` int(11) NOT NULL,
              `status` enum('available','occupied','maintenance','booked') DEFAULT 'available',
              `image_url` varchar(255) DEFAULT NULL,
              `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
              `category` varchar(50) DEFAULT 'standard',
              `count` int(255) DEFAULT NULL,
              `room_type` varchar(255) NOT NULL,
              `size` double NOT NULL,
              `amenities` text NOT NULL,
              PRIMARY KEY (`id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
            """,
            # 设施表
            """
            CREATE TABLE IF NOT EXISTS `amenities` (
              `id` int(11) NOT NULL AUTO_INCREMENT,
              `name` varchar(100) NOT NULL COMMENT '设施名称',
              `category` varchar(50) NOT NULL COMMENT '设施类别（如：基本设施、卫浴设施、媒体科技等）',
              `description` text DEFAULT NULL COMMENT '设施描述',
              `icon` varchar(50) DEFAULT NULL COMMENT '图标类名（用于前端展示）',
              `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
              `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
              PRIMARY KEY (`id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
            """,
            # 联系表单表
            """
            CREATE TABLE IF NOT EXISTS `contacts` (
              `id` int(11) NOT NULL AUTO_INCREMENT,
              `name` varchar(100) NOT NULL,
              `email` varchar(100) NOT NULL,
              `phone` varchar(20) DEFAULT NULL,
              `subject` varchar(200) NOT NULL,
              `message` text NOT NULL,
              `status` enum('pending','replied','resolved') DEFAULT 'pending',
              `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
              PRIMARY KEY (`id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
            """,
            # 网站设置表
            """
            CREATE TABLE IF NOT EXISTS `site_settings` (
              `id` int(11) NOT NULL AUTO_INCREMENT,
              `site_name` varchar(100) NOT NULL DEFAULT '风之宿',
              `site_description` text,
              `contact_phone` varchar(20),
              `contact_email` varchar(100),
              `address` varchar(255),
              `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
              `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
              PRIMARY KEY (`id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='网站基本设置表';
            """,
            # 轮播图表
            """
            CREATE TABLE IF NOT EXISTS `banners` (
              `id` int(11) NOT NULL AUTO_INCREMENT,
              `title` varchar(100) NOT NULL,
              `image_url` varchar(255) NOT NULL,
              `link_url` varchar(255),
              `sort_order` int(11) NOT NULL DEFAULT '0',
              `is_active` tinyint(1) NOT NULL DEFAULT '1',
              `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
              `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
              PRIMARY KEY (`id`),
              KEY `idx_sort_order` (`sort_order`),
              KEY `idx_is_active` (`is_active`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='轮播图表';
            """,
            # 关于我们内容表
            """
            CREATE TABLE IF NOT EXISTS `about_content` (
              `id` int(11) NOT NULL AUTO_INCREMENT,
              `title` varchar(100) NOT NULL DEFAULT '关于我们',
              `content` text NOT NULL,
              `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
              `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
              PRIMARY KEY (`id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='关于我们内容表';
            """,

            # 2. 创建依赖表（依赖上述基础表）
            # 预订表（依赖users和rooms）
            """
            CREATE TABLE IF NOT EXISTS `bookings` (
              `id` int(11) NOT NULL AUTO_INCREMENT,
              `user_id` int(11) NOT NULL,
              `room_id` int(11) NOT NULL,
              `check_in` date NOT NULL,
              `check_out` date NOT NULL,
              `guests` int(11) NOT NULL,
              `total_price` decimal(10,2) NOT NULL,
              `status` enum('pending','confirmed','checked_in','checked_out','cancelled') DEFAULT 'pending',
              `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
              PRIMARY KEY (`id`),
              KEY `user_id` (`user_id`),
              KEY `room_id` (`room_id`),
              CONSTRAINT `bookings_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
              CONSTRAINT `bookings_ibfk_2` FOREIGN KEY (`room_id`) REFERENCES `rooms` (`id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
            """,
            # 账单表（依赖bookings）
            """
            CREATE TABLE IF NOT EXISTS `bills` (
              `id` int(11) NOT NULL AUTO_INCREMENT,
              `booking_id` int(11) NOT NULL,
              `amount` decimal(10,2) NOT NULL,
              `status` enum('pending','paid') DEFAULT 'pending',
              `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
              `paid_at` timestamp NULL DEFAULT NULL,
              PRIMARY KEY (`id`),
              KEY `booking_id` (`booking_id`),
              CONSTRAINT `bills_ibfk_1` FOREIGN KEY (`booking_id`) REFERENCES `bookings` (`id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
            """,
            # 反馈表（依赖users）
            """
            CREATE TABLE IF NOT EXISTS `feedbacks` (
              `id` int(11) NOT NULL AUTO_INCREMENT,
              `user_id` int(11) NOT NULL,
              `title` varchar(255) NOT NULL,
              `description` text NOT NULL,
              `status` enum('pending','processing','resolved') DEFAULT 'pending',
              `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
              `resolved_at` timestamp NULL DEFAULT NULL,
              PRIMARY KEY (`id`),
              KEY `user_id` (`user_id`),
              CONSTRAINT `feedbacks_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
            """,
            # 房间-设施关联表（依赖rooms和amenities）
            """
            CREATE TABLE IF NOT EXISTS `room_amenities` (
              `id` int(11) NOT NULL AUTO_INCREMENT,
              `room_id` int(11) NOT NULL COMMENT '房间ID',
              `amenity_id` int(11) NOT NULL COMMENT '设施ID',
              `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
              PRIMARY KEY (`id`),
              UNIQUE KEY `unique_room_amenity` (`room_id`,`amenity_id`),
              KEY `amenity_id` (`amenity_id`),
              CONSTRAINT `room_amenities_ibfk_1` FOREIGN KEY (`room_id`) REFERENCES `rooms` (`id`) ON DELETE CASCADE,
              CONSTRAINT `room_amenities_ibfk_2` FOREIGN KEY (`amenity_id`) REFERENCES `amenities` (`id`) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
            """
        ]

        # 执行表创建语句
        for table_sql in tables:
            cursor.execute(table_sql)
            print(f"表创建成功: {table_sql.split('`')[1]}")  # 提取表名打印

        # 插入初始数据（使用INSERT IGNORE避免重复）
        # 用户数据
        users_data = [
            ('admin', 'admin123', '12345690@qq.com', None, 'admin', '2025-10-02 03:01:30'),
            ('hyadmin', 'admin123', '1234567@qq.com', None, 'admin', '2025-10-02 03:01:30'),
            ('lakiya', '123456', '123456@qq.com', '139123456789', 'user', '2025-10-02 03:29:08'),
            ('liwally', '123456', '1748000005@qq.com', '1360000', 'user', '2025-10-03 08:47:49')
        ]
        cursor.executemany("""
            INSERT IGNORE INTO users (username, password, email, phone, role, created_at) 
            VALUES (%s, %s, %s, %s, %s, %s)
        """, users_data)
        print("用户初始数据插入完成")

        # 房间数据
        rooms_data = [
            ('和风雅致套房', '传统日式风格套房，配备榻榻米和私人温泉，让您体验纯正的日式住宿文化。', 888.00, 2, 'available', 'rooms/20251002_162239_5.jpg', '2025-10-02 03:01:30', 'standard', None, '', 0, '空调'),
            ('竹林景观房', '窗外即是精心打理的日式庭院，竹林环绕，环境幽静，适合寻求宁静的旅客。', 688.00, 2, 'occupied', 'rooms/20251002_180107_2.jpg', '2025-10-02 03:01:30', 'standard', None, 'standard', 66, ''),
            ('家庭和室', '宽敞的和式房间，适合家庭入住，配备舒适的寝具和传统日式装饰。', 988.00, 4, 'available', 'rooms/20251003_155658_1_15.jpeg', '2025-10-02 03:01:30', 'standard', None, 'standard', 66, ''),
            ('山景豪华房', '坐拥山景的豪华客房，现代与传统的完美结合，提供极致的舒适体验。', 1288.00, 2, 'available', 'rooms/20251002_215036_1_14.jpeg', '2025-10-02 03:01:30', 'standard', None, 'deluxe', 100, ''),
            ('和风雅致套房', '传统日式风格套房，配备榻榻米和私人温泉，让您体验纯正的日式住宿文化。', 888.00, 2, 'available', 'rooms/20251002_162207_5.jpg', '2025-10-02 03:02:16', 'standard', None, '', 0, ''),
            ('家庭和室', '宽敞的和式房间，适合家庭入住，配备舒适的寝具和传统日式装饰。', 988.00, 4, 'available', 'rooms/20251003_155631_1_6.jpeg', '2025-10-02 03:02:16', 'standard', None, 'standard', 36.8, ''),
            ('山景豪华房', '坐拥山景的豪华客房，现代与传统的完美结合，提供极致的舒适体验。', 1288.00, 2, 'available', 'rooms/20251002_182424_2.jpg', '2025-10-02 03:02:16', 'standard', None, '', 0, ''),
            ('星空套房', '拥有透明屋顶的独特套房，夜晚可躺在床上仰望星空。现代化的设计配备智能家居系统，提供极致舒适的住宿体验。', 980.00, 2, 'occupied', 'rooms/20251002_175951_6.jpg', '2025-10-02 05:16:30', 'standard', None, 'suite', 36, ''),
            ('家庭套房', '适合家庭入住的宽敞套房，包含主卧和儿童房，配备完善的家庭设施。独立的起居空间让全家人都能享受舒适时光。', 1200.00, 4, 'available', 'rooms/20251002_180021_img.jpg', '2025-10-02 05:16:30', 'standard', None, '', 0, '')
        ]
        cursor.executemany("""
            INSERT IGNORE INTO rooms (name, description, price, capacity, status, image_url, created_at, category, count, room_type, size, amenities) 
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """, rooms_data)
        print("房间初始数据插入完成")

        # 设施数据
        amenities_data = [
            ('免费WiFi', '网络通讯', '房间内提供高速无线网络', 'fa-wifi', '2025-10-03 07:18:41', '2025-10-03 07:18:41'),
            ('空调', '基本设施', '独立温控空调系统', 'fa-snowflake-o', '2025-10-03 07:18:41', '2025-10-03 07:18:41'),
            ('电视', '媒体科技', '42寸高清智能电视', 'fa-television', '2025-10-03 07:18:41', '2025-10-03 07:18:41'),
            ('独立卫浴', '卫浴设施', '私人浴室配有淋浴', 'fa-bath', '2025-10-03 07:18:41', '2025-10-03 07:18:41'),
            ('吹风机', '卫浴设施', '客房提供吹风机', 'fa-birthday-cake', '2025-10-03 07:18:41', '2025-10-03 07:18:41'),
            ('24小时热水', '卫浴设施', '全天供应热水', 'fa-tint', '2025-10-03 07:18:41', '2025-10-03 07:18:41'),
            ('衣柜', '基本设施', '内置衣柜和衣架', 'fa-archive', '2025-10-03 07:18:41', '2025-10-03 07:18:41'),
            ('书桌', '办公设施', '工作书桌和座椅', 'fa-desktop', '2025-10-03 07:18:41', '2025-10-03 07:18:41'),
            ('矿泉水', '餐饮服务', '每日免费提供矿泉水', 'fa-tint', '2025-10-03 07:18:41', '2025-10-03 07:18:41'),
            ('拖鞋', '客房用品', '一次性拖鞋', 'fa-shoe-prints', '2025-10-03 07:18:41', '2025-10-03 07:18:41')
        ]
        cursor.executemany("""
            INSERT IGNORE INTO amenities (name, category, description, icon, created_at, updated_at) 
            VALUES (%s, %s, %s, %s, %s, %s)
        """, amenities_data)
        print("设施初始数据插入完成")

        # 预订数据（依赖users和rooms）
        bookings_data = [
            (3, 2, '2025-10-02', '2025-10-11', 9, 6192.00, 'checked_in', '2025-10-02 03:30:08'),
            (3, 1, '2025-10-02', '2025-10-03', 1, 888.00, 'cancelled', '2025-10-02 07:49:05'),
            (3, 1, '2025-10-02', '2025-10-17', 2, 13320.00, 'cancelled', '2025-10-02 10:26:04'),
            (5, 12, '2025-10-03', '2025-10-17', 3, 13720.00, 'checked_in', '2025-10-03 08:48:43')
        ]
        cursor.executemany("""
            INSERT IGNORE INTO bookings (user_id, room_id, check_in, check_out, guests, total_price, status, created_at) 
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
        """, bookings_data)
        print("预订初始数据插入完成")

        # 账单数据（依赖bookings）
        bills_data = [
            (1, 6192.00, 'paid', '2025-10-02 03:30:08', '2025-10-02 03:31:04'),
            (3, 13320.00, 'paid', '2025-10-02 10:26:04', '2025-10-02 12:24:01'),
            (4, 13720.00, 'paid', '2025-10-03 08:48:43', '2025-10-03 08:48:52')
        ]
        cursor.executemany("""
            INSERT IGNORE INTO bills (booking_id, amount, status, created_at, paid_at) 
            VALUES (%s, %s, %s, %s, %s)
        """, bills_data)
        print("账单初始数据插入完成")

        # 反馈数据（依赖users）
        feedbacks_data = [
            (3, '门打不开了', '门打不开了', 'processing', '2025-10-02 03:31:00', None)
        ]
        cursor.executemany("""
            INSERT IGNORE INTO feedbacks (user_id, title, description, status, created_at, resolved_at) 
            VALUES (%s, %s, %s, %s, %s, %s)
        """, feedbacks_data)
        print("反馈初始数据插入完成")

        # 房间-设施关联数据（依赖rooms和amenities）
        room_amenities_data = [
            (1, 6, '2025-10-03 07:23:30'),
            (1, 1, '2025-10-03 07:23:30'),
            (1, 3, '2025-10-03 07:24:00'),
            (1, 2, '2025-10-03 07:24:00'),
            (1, 7, '2025-10-03 07:24:30'),
            (1, 8, '2025-10-03 07:24:30')
        ]
        cursor.executemany("""
            INSERT IGNORE INTO room_amenities (room_id, amenity_id, created_at) 
            VALUES (%s, %s, %s)
        """, room_amenities_data)
        print("房间-设施关联数据插入完成")

        # 网站设置数据（避免重复插入）
        cursor.execute("""
            INSERT INTO `site_settings` (`site_name`, `site_description`, `contact_phone`, `contact_email`, `address`) 
            VALUES ('风之宿', '体验传统日式风情的舒适住宿', '0123-45-6789', 'info@kazenosyofuso.com', '〒123-4567 ○○县市川市市川市00-00')
            ON DUPLICATE KEY UPDATE `id`=`id`  -- 已存在则不更新
        """)

        # 关于我们内容数据
        cursor.execute("""
            INSERT INTO `about_content` (`title`, `content`) 
            VALUES ('关于风之宿', '<p>风之宿是一家传统日式旅馆，坐落于风景如画的山林之间。我们致力于为客人提供最纯正的日式住宿体验，让您在现代生活的喧嚣中寻得一片宁静之地。</p><p>我们的每一间客房都经过精心设计，融合了传统日式美学与现代舒适度。无论是榻榻米房间还是现代化套房，都能让您感受到家的温暖。</p>')
            ON DUPLICATE KEY UPDATE `id`=`id`
        """)

        conn.commit()
        cursor.close()
        conn.close()

        print("数据库初始化完成！所有表和数据已正确创建")
        return True
    except Exception as e:
        print(f"数据库初始化失败: {str(e)}")
        return False

if __name__ == '__main__':
    init_database()